"""
Case Type   : HTAP行列融合特性
Case Name   : 不支持外表，合理报错
Create At   : 2024/11/14
Owner       : ningyali
Description :
    1.生成指定用户密码的加密密码和加密因子文件
    2.创建用户和两个数据库
    3.库1进行本地表创建
    4.库2进行外表创建
    5.部分列转换
    6.取消列存
    7.查询
    8.全表转换
    9.查询
    10.取消列存
    11.清理数据环境
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.执行成功
    5.合理报错
    6.合理报错
    7.执行成功
    8.合理报错
    9.执行成功
    10.合理报错
    11.清理完成
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
from yat.test import macro


class HTAP(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh_1 = CommonSH('SingleDbAmode')
        self.pri_sh_2 = CommonSH('SingleDbUser')
        self.user = 'u_htap_single_0026'
        self.t_name = 't_htap_single_0026'
        self.expect_msg = f'ERROR:  "{self.t_name}_2" is a foreign table,' \
                          f' which does not support column constraints.'

    def test_trans(self):
        text = '----step1:生成指定用户密码的加密密码和加密因子文件;expect:执行成功----'
        self.log.info(text)
        cmd = f'source {macro.SINGLE_DB_ENV_PATH};' \
              f'gs_guc generate -S {macro.COMMON_PASSWD} ' \
              f'-D $GAUSSHOME/bin -o usermapping'
        self.log.info(cmd)
        q_res = self.pri_sh_1.node.sh(cmd).result()
        self.log.info(q_res)
        self.assertIn('gs_guc generate -S ***', q_res, f'执行失败:{text}')

        text = '----step2:创建用户和两个数据库;expect:执行成功----'
        self.log.info(text)
        sql = f"create user {self.user} identified " \
              f"by '{macro.COMMON_PASSWD}';" \
              f"grant all privileges to {self.user};"
        q_res = self.pri_sh_1.execut_db_sql(
            sql, env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(q_res)
        self.assertIn('CREATE ROLE', q_res, f'执行失败:{text}')
        self.assertIn('ALTER ROLE', q_res, f'执行失败:{text}')

        self.log.info('为新用户配置白名单连接')
        cmd = f'source {macro.SINGLE_DB_ENV_PATH};' \
              f'gs_guc reload -N all -I all -h "host all {self.user} ' \
              f'{self.pri_sh_1.node.db_host}/32 sha256";'
        self.log.info(cmd)
        exec_res = self.pri_sh_1.node.sh(cmd).result()
        self.log.info(exec_res)
        self.assertEqual(exec_res.count('Failed instances: 0'), 1,
                         f'执行失败:{text}')

        text = '----step3:库1进行本地表创建;expect:执行成功----'
        self.log.info(text)
        self.log.info('建立本地表')
        sql = f"drop table if exists {self.t_name}_1 cascade;" \
              f"create table {self.t_name}_1 (" \
              f"id int primary key,name char(30));" \
              f"insert into {self.t_name}_1 (name, id) values ('bob', 1);"
        sql_res = self.pri_sh_1.execut_db_sql(
            sql, env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(sql_res)
        self.assertIn('CREATE TABLE', sql_res, f'执行失败:{text}')
        self.assertIn('INSERT 0 1', sql_res, f'执行失败:{text}')

        text = '----step4:库2进行外表创建;expect:执行成功----'
        self.log.info(text)
        self.log.info('建立扩展')
        sql = f'create extension postgres_fdw;'

        self.log.info('建立服务')
        sql += f"create server pg_server " \
               f"foreign data wrapper postgres_fdw " \
               f"options (host '{self.pri_sh_1.node.db_host}', " \
               f"dbname '{self.pri_sh_1.node.db_name}', " \
               f"port '{self.pri_sh_1.node.db_port}');"

        self.log.info('建立用户映射')
        sql += f"create user mapping for {self.user} " \
               f"server pg_server options (user '{self.user}', " \
               f"password '{macro.COMMON_PASSWD}');"
        sql_res = self.pri_sh_2.execut_db_sql(
            sql, env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(sql_res)
        self.assertRegex(
            sql_res, r'CREATE EXTENSION\s+CREATE SERVER\s+CREATE USER MAPPING',
            f'执行失败:{text}')

        self.log.info('建立外表, 插入数据')
        sql = f"create foreign table {self.t_name}_2 (id int,name text) " \
              f"server pg_server " \
              f"options (table_name '{self.t_name}_1');" \
              f"insert into {self.t_name}_2 (name, id) values " \
              f"('tom', 2), ('jim', 3);"
        sql_res = self.pri_sh_2.execut_db_sql(
            sql, sql_type=f'-U {self.user} -W {macro.COMMON_PASSWD}',
            env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(sql_res)
        self.assertRegex(sql_res, r'CREATE FOREIGN TABLE\s+INSERT 0 2',
                         f'执行失败:{text}')

        text = '----step5:部分列转换;expect:合理报错----'
        self.log.info(text)
        sql = f'alter table {self.t_name}_2 imcstored(id);'
        q_res = self.pri_sh_2.execut_db_sql(
            sql, sql_type=f'-U {self.user} -W {macro.COMMON_PASSWD}',
            env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(q_res)
        self.assertIn(self.expect_msg, q_res, f'执行失败:{text}')

        text = '----step6:查询;expect:执行成功----'
        self.log.info(text)
        sql = f"set enable_imcsscan to on;" \
              f"explain select * from {self.t_name}_2 order by 1;" \
              f"select * from {self.t_name}_2 order by 1;"
        q_res = self.pri_sh_2.execut_db_sql(
            sql, sql_type=f'-U {self.user} -W {macro.COMMON_PASSWD}',
            env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(f'查询结果:\n{q_res}')
        self.assertIn('Foreign Scan', q_res, f'执行失败:{text}')
        self.assertIn('3 rows', q_res, f'执行失败:{text}')
        self.assertNotIn('Imcs Scan', q_res, f'执行失败:{text}')

        text = '----step7:取消列存;expect:合理报错----'
        self.log.info(text)
        sql = f'alter table {self.t_name}_2 unimcstored;'
        q_res = self.pri_sh_2.execut_db_sql(
            sql, sql_type=f'-U {self.user} -W {macro.COMMON_PASSWD}',
            env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(q_res)
        self.assertIn(self.expect_msg, q_res, f'执行失败:{text}')

        text = '----step8:全表转换;expect:合理报错----'
        self.log.info(text)
        sql = f'alter table {self.t_name}_2 imcstored;'
        q_res = self.pri_sh_2.execut_db_sql(
            sql, sql_type=f'-U {self.user} -W {macro.COMMON_PASSWD}',
            env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(q_res)
        self.assertIn(self.expect_msg, q_res, f'执行失败:{text}')

        text = '----step9:查询;expect:执行成功----'
        self.log.info(text)
        sql = f"set enable_imcsscan to on;" \
              f"explain select * from {self.t_name}_2 order by 1;" \
              f"select * from {self.t_name}_2 order by 1;"
        q_res = self.pri_sh_2.execut_db_sql(
            sql, sql_type=f'-U {self.user} -W {macro.COMMON_PASSWD}',
            env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(f'查询结果:\n{q_res}')
        self.assertIn('Foreign Scan', q_res, f'执行失败:{text}')
        self.assertIn('3 rows', q_res, f'执行失败:{text}')
        self.assertNotIn('Imcs Scan', q_res, f'执行失败:{text}')

        text = '----step10:取消列存;expect:合理报错----'
        self.log.info(text)
        sql = f'alter table {self.t_name}_2 unimcstored;'
        q_res = self.pri_sh_2.execut_db_sql(
            sql, sql_type=f'-U {self.user} -W {macro.COMMON_PASSWD}',
            env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(q_res)
        self.assertIn(self.expect_msg, q_res, f'执行失败:{text}')

    def tearDown(self):
        text = '----step11:清理环境;expect:执行成功----'
        self.log.info(text)
        sql = f"drop foreign table {self.t_name}_2;" \
              f"drop user mapping for {self.user} server pg_server;" \
              f"drop server pg_server;" \
              f"drop extension postgres_fdw cascade;"
        q_res1 = self.pri_sh_2.execut_db_sql(
            sql, env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(q_res1)

        sql = f'drop table if exists {self.t_name}_1 cascade;' \
              f'drop user if exists {self.user} cascade;'
        q_res2 = self.pri_sh_1.execut_db_sql(
            sql, env_path=macro.SINGLE_DB_ENV_PATH)
        self.log.info(q_res2)

        self.assertRegex(q_res1,
                         r'DROP FOREIGN TABLE\s+DROP USER MAPPING\s+'
                         r'DROP SERVER\s+DROP EXTENSION',
                         f'执行失败:{text}')
        self.assertRegex(q_res2,
                         r'DROP TABLE\s+DROP ROLE',
                         f'执行失败:{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
